MDX Function Listing

The following guide is a summary of the functions available in the Pyramid MDX Editor. The descriptions have been derived from the online MDX reference published by Microsoft.

For more detail on the structure of each function and how it should be used please check this reference and other third party materials.

MDX Function Name	Grouping	Type	Description
AddCalculatedMembers	Set	function	Returns a set generated by adding calculated members to a specified set.
Aggregate	Numeric	function	Returns a scalar value calculated by aggregating either measures or an optionally specified numeric expression over the tuples of a specified set.
AllMembers	Dimension	property	Returns a set that contains all members, including calculated members, of the specified dimension, hierarchy, or level.
AllMembers	Hierarchy	property	Returns a set that contains all members, including calculated members, of the specified dimension, hierarchy, or level.
AllMembers	Level	property	Returns a set that contains all members, including calculated members, of the specified dimension, hierarchy, or level.
Ancestor	Member	function	Returns the ancestor of a member at a specified level or distance.
Ancestors	Set	function	Returns a set of all ancestors of a member at a specified level or distance.
Ascendants	Set	function	Returns the set of the ascendants of a specified member, including the member itself.
Avg	Numeric	function	Returns the average value of measures or the average value of an optional numeric expression, evaluated over a specified set.
Axis	Set	function	Returns a set defined in an axis.
BottomCount	Set	function	Sorts a set in ascending order, and returns the specified number of tuples with the lowest values.
BottomPercent	Set	function	Sorts a set in ascending order, and returns a set of tuples with the lowest values whose cumulative total is equal to or less than a specified percentage.
BottomSum	Set	function	Sorts a set in ascending order, and returns a set of tuples with the lowest values whose total is equal to or less than a specified value.
CalculationCurrentPass	Numeric	function	Returns the current calculation pass of a cube for the specified query context.
CalculationPassValue	Numeric	function	Returns the value of a MDX expression evaluated over the specified calculation pass of a cube.
CalculationPassValue	Numeric	function	Returns the value of a MDX expression evaluated over the specified calculation pass of a cube.
Children	Set	property	Returns the children of a specified member.
ClosingPeriod	Member	function	Returns the last sibling among the descendants of a member at a specified level.
CoalesceEmpty	Numeric	function	Coalesces an empty cell value to a number or string and returns the coalesced value.
Correlation	Numeric	function	Returns the correlation coefficient of two series evaluated over a set.
Count	Numeric	function	Returns the number of cells in a set.
Count	Numeric	property	Returns the number of cells in a set.
Cousin	Member	function	Returns the child member with the same relative position under a parent member as the specified child member.
Covariance	Numeric	function	Returns the population covariance of two series evaluated over a set, using the biased population formula.
CovarianceN	Numeric	function	Returns the sample covariance of two series evaluated over a set, using the unbiased population formula.
Crossjoin	Set	function	Returns the cross product of one or more sets.
Current	Member	property	Returns the current tuple from a set during iteration.
CurrentMember	Dimension	property	Returns the current member along a specified dimension or hierarchy during iteration.
CurrentMember	Hierarchy	property	Returns the current member along a specified dimension or hierarchy during iteration.
CurrentOrdinal	Numeric	property	Returns the current iteration number within a set during iteration.
DataMember	Numeric	property	Returns the system-generated data member that is associated with a non-leaf member of a dimension.
DefaultMember	Member	property	Returns the default member of a dimension or hierarchy.
DefaultMember	Member	property	Returns the default member of a dimension or hierarchy.
Descendants	Set	function	Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
Descendants	Set	function	Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
Dimension	Hierarchy	property	Returns the dimension that contains a specified member, level, or hierarchy.
Dimension	Level	property	Returns the dimension that contains a specified member, level, or hierarchy.
Dimension	Member	property	Returns the dimension that contains a specified member, level, or hierarchy.
Dimensions	Dimension	function	Returns a hierarchy specified by a numeric or string expression.
Distinct	Set	function	Returns a set, removing duplicate tuples from a specified set.
DistinctCount	Numeric	function	Returns the number of distinct, nonempty tuples in a set.
DrilldownLevel	Set	function	Drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set.
DrilldownLevel	Set	function	Drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set.
DrilldownLevelBottom	Set	function	Drills down the bottommost members of a set, at a specified level, to one level below.
DrilldownLevelTop	Set	function	Drills down the topmost members of a set, at a specified level, to one level below.
DrilldownMember	Set	function	Drills down the members in a specified set that are present in a second specified set. Alternatively, the function drills down on a set of tuples.
DrilldownMemberBottom	Set	function	Drills down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of members. Alternatively, this function also drills down on a set of tuples.
DrilldownMemberTop	Set	function	Drills down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of members. Alternatively, this function drills down on a set of tuples.
DrillupLevel	Set	function	Drills up the members of a set that are below a specified level.
DrillupMember	Set	function	Drills up the members in a specified set that are present in a second specified set.
Error	Logical	function	Raises an error, optionally providing a specified error message.
Except	Set	function	Finds the difference between two sets, optionally retaining duplicates.
Exists	Set	function	Returns the set of members of one set that exist with one or more tuples of one or more other sets.
Extract	Set	function	Returns a set of tuples from extracted dimension elements.
Filter	Set	function	Returns the set that results from filtering a specified set based on a search condition.
FirstChild	Member	property	Returns the first child of a member.
FirstSibling	Member	property	Returns the first child of the parent of a member.
Generate	Set	function	Applies a set to each member of another set, and then joins the resulting sets by union. Alternatively, this function returns a concatenated string created by evaluating a string expression over a set.
Generate	Set	function	Applies a set to each member of another set, and then joins the resulting sets by union. Alternatively, this function returns a concatenated string created by evaluating a string expression over a set.
Head	Set	function	Returns the first specified number of elements in a set, while retaining duplicates.
Hierarchize	Set	function	Orders the members of a set in a hierarchy.
Hierarchy	Level	property	Returns the hierarchy that contains a specified member or level.
Hierarchy	Member	property	Returns the hierarchy that contains a specified member or level.
IIf	Logical	function	Returns one of two values determined by a logical test.
Intersect	Set	function	Returns the intersection of two input sets, optionally retaining duplicates.
IsAncestor	Logical	function	Returns whether a specified member is an ancestor of another specified member.
IsEmpty	Logical	function	Returns whether the evaluated expression is the empty cell value.
IsGeneration	Logical	function	Returns whether a specified member is in a specified generation.
IsLeaf	Logical	function	Returns whether a specified member is a leaf member.
IsSibling	Logical	function	Returns whether a specified member is a sibling of another specified member.
Item	Member	property	Returns a member from a specified tuple.
KPICurrentTimeMember	KPI	function	Returns the current time member of the specified Key Performance Indicator (KPI).
KPIGoal	KPI	function	Returns the member that calculates the value for the goal portion of the specified Key Performance Indicator (KPI).
KPIStatus	KPI	function	Returns the member that calculates the value for the status portion of the specified Key Performance Indicator (KPI).
KPITrend	KPI	function	Returns the member that calculates the value for the trend portion of the specified Key Performance Indicator (KPI).
KPIValue	KPI	function	Returns the member that calculates the value for the value portion of the specified Key Performance Indicator (KPI).
KPIWeight	KPI	function	Returns the weight of the specified Key Performance Indicator (KPI).
Lag	Member	property	Returns the member that is a specified number of positions before a specified member along the member's dimension.
LastChild	Member	property	Returns the last child of a specified member.
LastPeriods	Set	function	Returns a set of members up to and including a specified member.
LastSibling	Member	property	Returns the last child of the parent of a specified member.
Lead	Member	property	Returns the member that is a specified number of positions following a specified member along the member's dimension.
Leaves	Set	function	Returns the set of leaf members in the specified dimension, member, or tuple.
Level	Level	property	Returns the level of a member.
Levels	Member	property	Returns the level whose position in a dimension or hierarchy is specified by a numeric expression or whose name is specified by a string expression.
LinkMember	Member	function	Returns the member equivalent to a specified member in a specified hierarchy.
LinRegIntercept	Numeric	function	Calculates the linear regression of a set and returns the value of the intercept in the regression line, y = ax + b.
LinRegPoint	Numeric	function	Calculates the linear regression of a set and returns the value of y in the regression line, y = ax + b.
LinRegR2	Numeric	function	Calculates the linear regression of a set and returns the coefficient of determination, R2.
LinRegSlope	Numeric	function	Calculates the linear regression of a set, and returns the value of the slope in the regression line, y = ax + b.
LinRegVariance	Numeric	function	Calculates the linear regression of a set, and returns the variance associated with the regression line, y = ax + b.
LookupCube	Member	function	Returns the value of an MDX expression evaluated over another specified cube in the same database.
Max	Numeric	function	Returns the maximum value of a numeric expression that is evaluated over a set.
MeasureGroupMeasures	Set	function	Returns a set of measures that belongs to the specified measure group.
Median	Numeric	function	Returns the median value of a numeric expression that is evaluated over a set.
Members	Dimension	property	Returns the set of members in a dimension, level, or hierarchy.
Members	Hierarchy	property	Returns the set of members in a dimension, level, or hierarchy.
Members	Level	property	Returns the set of members in a dimension, level, or hierarchy.
Members	Set	function	Returns the set of members in a dimension, level, or hierarchy.
MemberToStr	String	function	Returns an MDX–formatted string that corresponds to a specified member.
Min	Numeric	function	Returns the minimum value of a numeric expression that is evaluated over a set.
Mtd	Set	function	Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level in the Time dimension.
Name	Dimension	property	Returns the name of a dimension, hierarchy, level, or member.
Name	Hierarchy	property	Returns the name of a dimension, hierarchy, level, or member.
Name	Level	property	Returns the name of a dimension, hierarchy, level, or member.
Name	Member	property	Returns the name of a dimension, hierarchy, level, or member.
NameToSet	Set	function	Returns a set that contains the member specified by an MDX–formatted string.
NextMember	Member	property	Returns the next member in the level that contains a specified member.
NonEmptyCrossjoin	Set	function	Returns the cross product of one or more sets as a set, excluding empty tuples and tuples without associated fact table data.
OpeningPeriod	Member	function	Returns the first sibling among the descendants of a specified level, optionally at a specified member.
Order	Set	function	Arranges members of a specified set, optionally preserving or breaking the hierarchy.
Ordinal	Numeric	property	Returns the zero-based ordinal value associated with a level.
ParallelPeriod	Member	function	Returns a member from a prior period in the same relative position as a specified member.
Parent	Member	property	Returns the parent of a member.
PeriodsToDate	Set	function	Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by a specified level in the Time dimension.
Predict	Numeric	function	Returns a value of a numeric expression evaluated over a data mining model.
PrevMember	Member	property	Returns the previous member in the level that contains a specified member.
Properties	Numeric	property	Returns a string, or a strongly-typed value, that contains a member property value.
Qtd	Set	function	Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Quarter level in the Time dimension.
Rank	Numeric	function	Returns the one-based rank of a specified tuple in a specified set.
RollupChildren	Numeric	function	Returns a value generated by rolling up the values of the children of a specified member using the specified unary operator.
Root	Member	function	Returns a tuple that consists of the All members from each attribute hierarchy in a cube, dimension, or tuple.
SetToArray	Other	function	Converts one or more sets to an array for use in a user-defined function.
SetToStr	String	function	Returns an MDX-formatted string of that corresponds to a specified set.
Siblings	Set	property	Returns the siblings of a specified member, including the member itself.
Stddev	Numeric	function	Alias for Stdev (MDX).
StddevP	Numeric	function	Alias for StdevP (MDX).
Stdev	Numeric	function	Returns the sample standard deviation of a numeric expression evaluated over a set, using the unbiased population formula.
StdevP	Numeric	function	Returns the population standard deviation of a numeric expression evaluated over a set, using the biased population formula.
StripCalculatedMembers	Set	function	Returns a set generated by removing calculated members from a specified set.
StrToMember	Member	function	Returns the member specified by an MDX–formatted string.
StrToSet	Set	function	Returns the set specified by an MDX–formatted string.
StrToTuple	Tuple	function	Returns the tuple specified by an MDX–formatted string.
StrToValue	Numeric	function	Returns the value specified by an MDX–formatted string.
Subset	Set	function	Returns a subset of tuples from a specified set.
Sum	Numeric	function	Returns the sum of a numeric expression evaluated over a set.
Tail	Set	function	Returns a subset from the end of a set.
ToggleDrillState	Set	function	Toggles the drill state of members.
TopCount	Set	function	Sorts a set in descending order and returns the specified number of elements with the highest values.
TopPercent	Set	function	Sorts a set in descending order, and returns a set of tuples with the highest values whose cumulative total is equal to or less than a specified percentage.
TopSum	Set	function	Sorts a set and returns the topmost elements whose cumulative total is at least a specified value.
TupleToStr	Tuple	function	Returns an MDX–formatted string that corresponds to specified tuple.
Union	Set	function	Returns the union of two sets, optionally retaining duplicates.
UniqueName	Dimension	property	Returns the unique name of a specified dimension, hierarchy, level, or member.
UniqueName	Hierarchy	property	Returns the unique name of a specified dimension, hierarchy, level, or member.
UniqueName	Level	property	Returns the unique name of a specified dimension, hierarchy, level, or member.
UniqueName	Member	property	Returns the unique name of a specified dimension, hierarchy, level, or member.
UnknownMember	Member	property	Returns the unknown member associated with a level or member.
UnknownMember	Member	property	Returns the unknown member associated with a level or member.
Unorder	Set	function	Removes any enforced ordering from a specified set.
UserName	String	function	Returns the domain name and user name of the current connection.
ValidMeasure	Member	function	Returns a valid measure in a virtual cube by forcing inapplicable dimensions to their top level.
Value	Member	property	Returns the value of a measure.
Var	Numeric	function	Returns the sample variance of a numeric expression evaluated over a set, using the unbiased population formula.
Variance	Numeric	function	Alias for Var (MDX).
VarianceP	Numeric	function	Alias for VarP (MDX).
VarP	Numeric	function	Returns the population variance of a numeric expression evaluated over a set, using the biased population formula.
VisualTotals	Set	function	Returns a set generated by dynamically totaling child members in a specified set, optionally using a pattern for the name of the parent member in the resulting cellset.
Wtd	Set	function	Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Week level in the Time dimension.
Ytd	Set	function	Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level in the Time dimension.